package com.mbc.rmt.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.mbc.common.dao.DAO;
import com.mbc.rmt.entities.AllBranchOpening;
import com.mbc.rmt.entities.AllBranchOpeningRpt;

public class AllBranchOpeningDAOImpls extends DAO<AllBranchOpening> implements
		AllBranchOpeningDAO {

	public AllBranchOpeningDAOImpls(Connection conn) {
		this.conn = conn;
		this.table_name = "AllBranchOpening";
	}

	@Override
	public void create(AllBranchOpening entity) throws SQLException {
		query = " insert into "
				+ table_name
				+ " ( AllBranchOpeningID, BranchID, DefaultBranchID, UserID, CurrencyID, CurrencyRateID, Rate, SourceReceivableAmount, SourcePayableAmount, HomeReceivableAmount, HomePayableAmount) values( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getAllBranchOpeningId());
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getDefaultBranchId());
		pstmt.setLong(i++, entity.getUserId());
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setLong(i++, entity.getCurrencyRateId());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getSourceReceivableAmt());
		pstmt.setDouble(i++, entity.getSourcePayableAmt());
		pstmt.setDouble(i++, entity.getHomeReceivableAmt());
		pstmt.setDouble(i++, entity.getHomePayableAmt());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(AllBranchOpening entity) throws SQLException {
		query = "update "
				+ table_name
				+ " set BranchID = ?, DefaultBranchID = ?, UserID = ?, CurrencyID = ?, CurrencyRateID = ?, Rate = ?, SourceReceivableAmount = ?, SourcePayableAmount = ?, HomeReceivableAmount = ?, HomePayableAmount = ? "
				+ " where AllBranchOpeningID = "
				+ entity.getAllBranchOpeningId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getBranchId());
		pstmt.setLong(i++, entity.getDefaultBranchId());
		pstmt.setLong(i++, entity.getUserId());
		pstmt.setLong(i++, entity.getCurrencyId());
		pstmt.setLong(i++, entity.getCurrencyRateId());
		pstmt.setDouble(i++, entity.getRate());
		pstmt.setDouble(i++, entity.getSourceReceivableAmt());
		pstmt.setDouble(i++, entity.getSourcePayableAmt());
		pstmt.setDouble(i++, entity.getHomeReceivableAmt());
		pstmt.setDouble(i++, entity.getHomePayableAmt());

		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(AllBranchOpening entity) throws SQLException {
        String col_key="";
		
		if(entity.getAllBranchOpeningId() !=0)
			col_key = " where AllBranchOpeningID = " + entity.getAllBranchOpeningId();
		
		query = getDeleteQuery() + col_key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();

	}

	@Override
	protected AllBranchOpening readRecord(ResultSet rs) throws SQLException {
		AllBranchOpening l_entity = new AllBranchOpening();
		l_entity.setAllBranchOpeningId(rs.getLong("AllBranchOpeningID"));
		l_entity.setBranchId(rs.getLong("BranchID"));
		l_entity.setBranchCode(rs.getString("BranchCode"));
		l_entity.setBranchName(rs.getString("Name"));
		l_entity.setDefaultBranchId(rs.getLong("DefaultBranchID"));
		l_entity.setUserId(rs.getLong("UserID"));
		l_entity.setCurrencyId(rs.getLong("CurrencyID"));
		l_entity.setCurrencyCode(rs.getString("CurrencyCode"));
		l_entity.setCurrencyRateId(rs.getLong("CurrencyRateID"));
		l_entity.setCurrencyRateCode(rs.getString("CurrencyRateCode"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setRateOperator(rs.getString("Roper"));
		l_entity.setSourceReceivableAmt(rs.getDouble("SourceReceivableAmount"));
		l_entity.setSourcePayableAmt(rs.getDouble("SourcePayableAmount"));
		l_entity.setHomeReceivableAmt(rs.getDouble("HomeReceivableAmount"));
		l_entity.setHomePayableAmt(rs.getDouble("HomePayableAmount"));
		return l_entity;
	}

	@Override
	public ArrayList<AllBranchOpening> find(long key) throws SQLException {
		ArrayList<AllBranchOpening> l_list = new ArrayList<AllBranchOpening>();
		query = " DECLARE @homecurrencyid bigint " +
				" SET @homecurrencyid=(SELECT HomeCurrencyid FROM Setup) "+
				" select ROW_NUMBER() over(order by a.AllBranchOpeningID)No, " +
				" a.AllBranchOpeningID,a.BranchID,b.BranchCode,b.Name, " +
				" a.DefaultBranchID, a.UserID,a.CurrencyID,c.CurrencyCode, " +
				" isnull(cr.CurrencyRateID,0)CurrencyRateID, " +
				" (CASE WHEN cr.CurrencyRateCode IS NULL AND @homecurrencyid=a.CurrencyID " +
				" THEN c.CurrencyCode + c.CurrencyCode ELSE cr.CurrencyRateCode END )currencyratecode, " +
				" a.Rate,cr.Roper, " +
				" a.SourceReceivableAmount,a.SourcePayableAmount, " +
				" a.HomeReceivableAmount,a.HomePayableAmount " +
				" from AllBranchOpening a " +
				" left join Branch b on b.BranchId = a.BranchID " +
				" left join [User] u on u.UserId = a.UserID " +
				" left join Currency c on c.CurrencyId=a.CurrencyID " +
				" left join CurrencyRate cr on cr.sourcecurrencyid=a.CurrencyID " + 
				" and cr.ConvertCurrencyId=@homecurrencyid "  +
				" and cr.BranchId= a.DefaultBranchID " +
				" where a.DefaultBranchID = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next()) {
			do {
				l_list.add(readRecord(rs));
			} while (rs.next());
		}
		rs.close();
		pstmt.close();
		return l_list;
	}
	
	protected AllBranchOpening readRecords(ResultSet rs) throws SQLException {
		AllBranchOpening l_entity = new AllBranchOpening();
		l_entity.setBranchId(rs.getLong("BranchID"));
		l_entity.setBranchCode(rs.getString("BranchCode"));
		l_entity.setBranchName(rs.getString("Name"));
		l_entity.setCurrencyId(rs.getLong("CurrencyID"));
		l_entity.setCurrencyCode(rs.getString("CurrencyCode"));
		l_entity.setCurrencyRateId(rs.getLong("CurrencyRateID"));
		l_entity.setCurrencyRateCode(rs.getString("CurrencyRateCode"));
		l_entity.setRate(rs.getDouble("Rate"));
		l_entity.setSourceReceivableAmt(rs.getDouble("SourceReceivableAmount"));
		l_entity.setSourcePayableAmt(rs.getDouble("SourcePayableAmount"));
		l_entity.setHomeReceivableAmt(rs.getDouble("HomeReceivableAmount"));
		l_entity.setHomePayableAmt(rs.getDouble("HomePayableAmount"));
		return l_entity;
	}

	@Override
	public AllBranchOpening getAllBranchOpening(AllBranchOpening entity)
			throws SQLException {
		AllBranchOpening branchOpening = null;
		query = " select " + 
				" abo.*,b.BranchCode,b.Name,c.CurrencyCode,cr.CurrencyRateCode,cr.Roper " +
				" from AllBranchOpening abo " +
				" left join Branch b on b.BranchId= abo.BranchID " +
				" left join Currency c on c.CurrencyId=abo.CurrencyID " +
				" left join CurrencyRate cr on cr.CurrencyRateId=abo.CurrencyRateID " +
				" where abo.AllBranchOpeningID = " + entity.getAllBranchOpeningId();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if (rs.next())
			branchOpening = readRecord(rs);
		rs.close();
		pstmt.close();
		return branchOpening;
	}

	@Override
	public ResultSet prepareAllBranchOpeningReport(AllBranchOpeningRpt info)
			throws SQLException {
		ResultSet l_rs;
		String l_query = "dbo.sp_cr_AllBranchOpeningRpt ?, ?, ?, ?, ?, ?";

		CallableStatement l_pstmt = conn.prepareCall(l_query);
		AllBranchOpeningRptParameter(l_pstmt, info);
		l_rs = l_pstmt.executeQuery();
		return l_rs;
	}

	private static void AllBranchOpeningRptParameter(CallableStatement l_pstmt,
			AllBranchOpeningRpt info) throws SQLException {
		int i = 1;

		l_pstmt.setString(i++, info.getFromBranchID());
		l_pstmt.setString(i++, info.getToBranchID());
		l_pstmt.setString(i++, info.getFromCurrencyID());
		l_pstmt.setString(i++, info.getToCurrencyID());
		l_pstmt.setString(i++, info.getOrderby());
		l_pstmt.setLong(i++, info.getBranchID());
	}
}
